/**
 * 基于 POI 技术 Excel 读写工具类
 * Excel row 取值范围: 0~总行数-1
 * by @author lms 2020.06.22 
 * 
 * 参考: https://blog.csdn.net/Joye_zy/article/details/135079774
 * 		 https://blog.csdn.net/maudboy/article/details/133145278
 */
package ms.core.tool;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

public class ExcelTool {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    private enum XLS_TYPE {xls, xlsx}

    private File file = null;
    private Workbook wb = null;
    private XLS_TYPE xlsType;

    /**
     * 按磁盘路径实例化
     * @param excelFile
     * @return
     */
	public static ExcelTool getInstance(String excelFile) {
		ExcelTool ret = new ExcelTool(excelFile);
		return ret;
	}

	ExcelTool(String excelFile){
		file = new File(excelFile);
		wb = getWorkbook(file);
	}

	private Workbook getWorkbook(File file) {
		Workbook wb = null;
		try {
			FileInputStream in = new FileInputStream(file);
			String fileName = file.getName().toLowerCase();
			//Excel2003
			if(fileName.endsWith(EXCEL_XLS)){     		
				wb = new HSSFWorkbook(in);
				xlsType = XLS_TYPE.xls;
			} //Excel 2007/2010
			else if(fileName.endsWith(EXCEL_XLSX)){
				wb = new XSSFWorkbook(in);
				xlsType = XLS_TYPE.xlsx;
			}
			return wb;
		}catch(Exception e) {
			Log4j2Tool.error(ExcelTool.class, "getWorkbook:", e);
			return null;
		}
	}

	/**
	 * 获取sheet的记录数
	 * 
	 * @return
	 */
	public int getRows(int sheetIndex) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		return sheet.getLastRowNum()-sheet.getFirstRowNum()+1;
	}

	/**
	 * 写入单元格
	 * @param sheet
	 * @param row >=0 0表示第一行
	 * @param col >=0 0表示第一列
	 * @param data
	 */
	private void writeCell(Sheet sheet, int row, int col, Object data) {
		try {
			Row r;
			if (sheet.getLastRowNum()<row) 
				r = sheet.createRow(row);
			else 
				r = sheet.getRow(row);

			Cell cell = r.createCell(col);
			cell.setCellValue(data.toString());
		}catch(Exception e) {
			Log4j2Tool.error(ExcelTool.class, e);
		}
	}
	
	/**
	 * 写入单元格
	 * @param sheet
	 * @param row >=0 0表示第一行
	 * @param col >=0 0表示第一列
	 * @param data
	 */
	public void writeCell(int sheetIndex, int row, int col, Object data) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		writeCell(sheet, row, col, data);
	}

	//写入一行记录
	public void writeRow(int sheetIndex, int row, List<Object> data) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		for(int i=0; i<data.size(); i++) {
			writeCell(sheet, row, i, data.get(i));
		}
	}

	/**
	 * 保存excel
	 */
	public void save() {
		try {
			//写入文件
			OutputStream out = new FileOutputStream(file);
			wb.write(out);
			out.flush();
			out.close();
		}catch(Exception e) {
			Log4j2Tool.error(ExcelTool.class, "save:", e);
		}
	}
	
	/**
	 * 读取指定行记录
	 * 
	 * @param titles
	 * @param row
	 * @return
	 */
	private Map<String, Object> readRowData(List<String> titles, Row row){
		if (titles==null || row==null) return null;

		Map<String, Object> ret = new HashMap<String, Object>();

		for (Iterator<Cell> iterator = row.cellIterator(); iterator.hasNext();) {
			Cell cell=(Cell) iterator.next();
			//System.out.println(cell.getAddress().toString());  Excel 列真实地址，比如A1,B1,...

			if (cell.getColumnIndex()>titles.size()-1) break;
			switch(cell.getCellType()) {
				case STRING:
					ret.put(titles.get(cell.getColumnIndex()), cell.getStringCellValue());
					break;
				case NUMERIC:
					ret.put(titles.get(cell.getColumnIndex()), cell.getNumericCellValue());
					break;
				case FORMULA:
					ret.put(titles.get(cell.getColumnIndex()), cell.getCellFormula());
					break;
				default :
					ret.put(titles.get(cell.getColumnIndex()), cell.getStringCellValue());
					break;
			}
		}
		
		return ret;
	}

	/**
	 * 读取标题(标题无合并单元格)
	 * 
	 * @param sheetIndex sheet索引Id
	 * @param titleRow
	 * @return
	 */
	public List<String> readTitleRow(int sheetIndex, int titleRow){
		Sheet sheet = wb.getSheetAt(sheetIndex);		
		if (titleRow>sheet.getLastRowNum()) 
			return null;
		
		List<String> title = new ArrayList<>();		
		Row row = sheet.getRow(titleRow);
		if (row==null) return title;
		
		for (Iterator<Cell> iterator = row.cellIterator(); iterator.hasNext();) {
			Cell cell=(Cell) iterator.next();
			title.add(cell.getStringCellValue());
		}

		return title;	
	}

	/**
	 * 读取标题(标题存在合并单元格)
	 * 
	 * @param sheetIndex sheet索引，从0开始
	 * @param titleStart 标题行起始
	 * @param titleEnd 标题行截止
	 * @return
	 */
	public List<String> readTitleRow(int sheetIndex, int titleStart, int titleEnd){
		Sheet sheet = wb.getSheetAt(sheetIndex);		
		if (titleStart<0 || titleEnd>sheet.getLastRowNum()) 
			return null;
		
		List<String> title = new ArrayList<>();									// 合并后标题行
		List<List<String>> parentTitles = new ArrayList<>();					// 原始多标题行
		for(int i=titleStart; i<=titleEnd; i++) {
			if (i==titleEnd) title = readTitleRow(sheetIndex, i);
			else parentTitles.add(readTitleRow(sheetIndex, i));
		}
		
		for(int i=parentTitles.size()-1; i>=0; i--) {
			List<String> columns = parentTitles.get(i);

			// 自底向上合并标题
			for(int col=0; col<title.size(); col++) {
				String column = columns.get(col);
				if (title.size()<col+1) title.add(column);
				// 如果合并标题列为空，说明标题未出现，直接以父标题为标题
				if (StrTool.isBlank(title.get(col))) title.set(col, column);
				else {
					if (StrTool.isBlank(column)) {
						for(int j=col; j>=0; j--) {
							column = columns.get(j);
							if (!StrTool.isBlank(column)) break;
						}
					}
					title.set(col, column+"."+title.get(col));
				}
			}
		}
		
		return title;
	}

	/**
	 * 读取excel指定行(解决一个excel文件超大,分批读取)
	 * 
	 * @param sheetIndex sheet索引页
	 * @param titles
	 * @param row 读取的起始行
	 * @param rows 读取的行数
	 * @return
	 */
	public List<Map<String, Object>> readExcel(int sheetIndex, List<String> titles, int row, int rows){
		Sheet sheet = wb.getSheetAt(0);		
		if (row>sheet.getLastRowNum()) 
			return null;

		List<Map<String, Object>> rets = new ArrayList<Map<String, Object>>();		
		for(int i=0; i<rows; i++) {
			if (row+i>sheet.getLastRowNum())
				break;

			Row xlsRow = sheet.getRow(row+i);
			Map<String, Object> data = readRowData(titles, xlsRow);
			if (data!=null) rets.add(data);
		}
		return rets;
	}

	/**
	 * 读取excel所有行
	 * 
	 * @return
	 */
	public List<Map<String, Object>> readTitleExcel(int sheetIndex, int titleStart, int titleEnd) {
		List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
		List<String> titles = readTitleRow(sheetIndex, titleStart, titleEnd);
		Sheet sheet = wb.getSheetAt(sheetIndex);

		int rows = 0;
		for (Iterator<Row> rowIterator=sheet.iterator(); rowIterator.hasNext();) {
			Row row=(Row) rowIterator.next();

			// 标题列
			if (rows<=titleEnd) {
				rows ++;
				continue;
			}
		
			Map<String, Object> newRow = readRowData(titles, row);
			if (newRow!=null) ret.add(newRow);
			rows ++;
		}
		return ret;
	}

	/**
	 * 获取Excel浮动图片和位置 (xls版)
	 * 
	 * @param sheet
	 * @return
	 * @throws IOException
	 */
	private Map<String, PictureData> getFloatingPictures(HSSFSheet sheet) throws IOException {
		Map<String, PictureData> map = new HashMap<String, PictureData>();
		HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
		if (patriarch==null)
			return map;

		List<HSSFShape> list = patriarch.getChildren();
		for (HSSFShape shape : list) {
			if (shape instanceof HSSFPicture) {
				HSSFPicture picture = (HSSFPicture) shape;
				HSSFClientAnchor cAnchor = picture.getClientAnchor();
				PictureData pdata = picture.getPictureData();
				String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
				map.put(key, pdata);
			}
		}

		return map;
	}

	/**
	 * 获取Excel浮动图片和位置 (xlsx版)
	 * 
	 * @param sheet
	 * @return
	 * @throws IOException
	 */
	private Map<String, PictureData> getFloatingPictures(XSSFSheet sheet) throws IOException {
		Map<String, PictureData> map = new HashMap<String, PictureData>();

		List<POIXMLDocumentPart> list = sheet.getRelations();
		for (POIXMLDocumentPart part : list) {
			if (part instanceof XSSFDrawing) {
				XSSFDrawing drawing = (XSSFDrawing) part;
				List<XSSFShape> shapes = drawing.getShapes();
				for (XSSFShape shape : shapes) {
					XSSFPicture picture = (XSSFPicture) shape;
					XSSFClientAnchor anchor = picture.getPreferredSize();
					CTMarker marker = anchor.getFrom();
					String key = marker.getRow() + "-" + marker.getCol();
					map.put(key, picture.getPictureData());
				}
			}
		}

		return map;
	}
	
	/**
	 * 读取excel图片
	 * 
	 * @param sheetIndex
	 * @return
	 * @throws IOException
	 */
	public Map<String, PictureData> getFloatingPictures(int sheetIndex) throws IOException {
		Sheet sht = wb.getSheetAt(sheetIndex);
		if (xlsType==XLS_TYPE.xls)
			return getFloatingPictures((HSSFSheet) sht);
		else
			return getFloatingPictures((XSSFSheet) sht);
	}

	public static void main(String[] args) throws IOException {
		// 读取文本Excel
		ExcelTool tool = ExcelTool.getInstance("d:\\ContainerImport.xlsx");
		List<Map<String, Object>> list = tool.readTitleExcel(0, 1, 1);
		System.out.println(JsonTool.listBeanToJson(list, true));
		// 读取图片
		Map<String, PictureData> map = tool.getFloatingPictures(0);
		System.out.println(map);
		
		System.out.println(DateTimeTool.doubleToDate(1000.9088));
	}
}
